﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Csharpzuoye
{
    class DBUser
    {
        //Main()函数调用实体类（静态/实例？）方法，连接数据库，控制台模拟输入输出，实现以下功能：
        //注册/登录：User类中的
        //LogOn()：使用SQL语句
        //Register()：使用存储过程bp_user_register
        //内容：Content类的
        //发布/修改：Publish()/Edit()，至少包含作者
        //单页呈现：Show()，根据Id输出内容的标题、正文、作者、评论等
        //列表页呈现：包括过滤（作者/文章系列/求助状态）和分页
        //消息批量标记为已读/删除：Message类的Read()和Delete()方法
        //用事务完成帮帮币的交易：？？？类的Sale()方法

        private const string connectionString = @"Data Source=(localdb)\ProjectsV13;
                                    Initial Catalog=17bang;Integrated Security=True";

        public IDbConnection GetNewconnection()
        {
            return new SqlConnection(connectionString);
        }

        public int Register(string username, string password, int inviter, int invitecode)
        {
            using (IDbConnection connection = GetNewconnection())
            {
                connection.Open();
                IDbCommand command = new SqlCommand();

                command.CommandType = CommandType.StoredProcedure;
                command.Connection = connection;
                command.CommandText = "TRegister";

                IDataParameter pusername = new SqlParameter("@username", username);
                command.Parameters.Add(pusername);

                IDataParameter ppassword = new SqlParameter("@password", password);
                command.Parameters.Add(ppassword);

                IDataParameter pinviter = new SqlParameter("@inviter", inviter);
                command.Parameters.Add(pinviter);

                IDataParameter pinvitecode = new SqlParameter("@invitecode", invitecode);
                command.Parameters.Add(pinvitecode);

                command.ExecuteNonQuery();
            }
            return 0;
        }
        public int LoGon(string username, string password)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                IDbCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = $"SELECT[Password] FROM [DBUser] WHERE [UserName] = @username";
                IDataParameter pusername = new SqlParameter("@username", username);
                command.Parameters.Add(pusername);

                String dbPassword = command.ExecuteScalar()?.ToString();

                if (dbPassword == null)
                {
                    Console.WriteLine("*用户名不存在*");
                }
                if (dbPassword != password)
                {
                    Console.WriteLine("*用户名或密码错误*");
                }
                return LoGon(username, password);
            }
        }


        private int ExecuteNonQurey(string cmdText, params IDataParameter[] parameters)
        {
            using (IDbConnection connection = GetNewconnection())
            {
                try
                {
                    connection.Open();
                    IDbCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = cmdText;
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        command.Parameters.Add(parameters[i]);

                    }
                    return command.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        //发布、修改文章
        public int Publish(string cmdText, params IDataParameter[] parameters)
        {
            return ExecuteNonQurey(cmdText, parameters);
        }

        public int Edit(string cmdText, params IDataParameter[] parameters)
        {
            return ExecuteNonQurey(cmdText, parameters);

        }
        //单页和多页的呈现
        public int Show(string cmdText, params IDataParameter[] parameters)
        {
            return ExecuteNonQurey(cmdText, parameters);

        }
        //批量删除
        public void DeleteRange(IDbCommand[] commands)
        {
            using (IDbConnection connection = GetNewconnection())
            {
                connection.Open();
                for (int i = 0; i < commands.Length; i++)
                {
                    commands[i].Connection = connection;
                    commands[i].ExecuteNonQuery();
                }
            }
        }

        public int Sale(User user)
        {
            using (IDbConnection connection = GetNewconnection())
            {
                connection.Open();
                using (IDbTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        IDbCommand command = new SqlCommand();
                        command.Connection = connection;
                        command.Transaction = transaction;
                        command.CommandText = "UPDATE BangMoney SET Balance -= 5";
                        command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                    return user.Id;

                }
            }
        }
    }
}
 
